<?php
class Location {

    public function __construct() {
        $this->SQL = MySQL::getInstance();
    }

    /*
     * getAll - get all locations
     * @return array data of form array(id, name, route_count)
     */
    public function getAll() {
        return $this->SQL->fquery('
            SELECT l.id, l.name, COALESCE(COUNT(r.id), 0) AS route_count
            FROM locations l
            LEFT JOIN routes r
                ON r.location_id = l.id
                AND r.removed = 0
            GROUP BY l.id
            ORDER BY l.name'
        );
    }

    /*
     * get - get all information on a location
     * @param id int location id
     * @return array data of form array(name, description) or FALSE
     */
    public function get($id) {
        if(!$id) {
            return FALSE;
        }
        return $this->SQL->fetch_single('
            SELECT id, name, description
            FROM locations
            WHERE id = %d
            LIMIT 1',
            $id
        );
    }

    /*
     * add - add a new location to the database
     * @param data array of form array(name, description)
     * @return int location id, or FALSE
     */
    public function add($data) {
        if(!isset($data['name']) || !isset($data['description']) || !$data['name'] || !$data['description']) {
            return FALSE;
        }
        return $this->SQL->fquery('
            INSERT INTO locations
            (name, description)
            VALUES
            ("%s", "%s")',
            addslashes($data['name']),
            addslashes($data['description'])
        );
    }

    /*
     * update - update the information on a location
     * @param id int location id
     * @param data array of form array(name, description)
     * @return bool TRUE/FALSE
     */
    public function update($id, $data) {
        if(!$id || !isset($data['name']) || !isset($data['description']) || !$data['name']) {
            return FALSE;
        }
        return $this->SQL->fquery('
            UPDATE locations
            SET name = "%s",
                description = "%s"
            WHERE id = %d
            LIMIT 1',
            addslashes($data['name']),
            addslashes($data['description']),
            $id
        );
    }

    /*
     * getRouteCount - get the number of routes at a location
     * @param locationId int location id
     * @return int number of routes
     */
    public function getRouteCount($locationId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM routes
            WHERE location_id = %d
            LIMIT 1',
            $locationId
        );
    }

    /*
     * getUserLinkCount - get the number of checkins for a location
     * @param locationId int location id
     * @return int number of checkins for this location
     */
    public function getUserLinkCount($locationId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM users_routes_link url
            JOIN routes r
                ON r.id = url.route_id
            WHERE r.location_id = %d
            LIMIT 1',
            $locationId
        );
    }

    /*
     * getAuthorCount - get number of authors for a location
     * @param locationId int location id
     * @return int number of authors
     */
    public function getAuthorCount($locationId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM authors
            WHERE location_id = %d
            LIMIT 1',
            $locationId
        );
    }

    /*
     * getUserCount - get number of unique users who checked in at this location
     * @param locationId int location id
     * @return int number of users
     */
    public function getUserCount($locationId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(DISTINCT url.user_id)
            FROM users_routes_link url
            JOIN routes r
                ON r.id = url.route_id
            WHERE r.location_id = %d
            LIMIT 1',
            $locationId
        );
    }

    /*
     * getNewestRoute - get the newest route added to a location
     * @param locationId int location id
     * @return array data of form array(id, difficulty, colour, is_short, author_id, author, created, name
     */
    public function getNewestRoute($locationId) {
        return $this->SQL->fetch_single('
            SELECT r.id, d.name AS difficulty, c.name AS colour, r.is_short, a.id AS author_id, a.name AS author,
                NULLIF(r.created, "0000-00-00") AS created, r.name
            FROM routes r
            JOIN difficulties d
                ON d.id = r.difficulty_id
            JOIN colours c
                ON c.id = r.colour_id
            LEFT JOIN authors a
                ON a.id = r.author_id
            WHERE r.location_id = %d
            ORDER BY r.created DESC
            LIMIT 1',
            $locationId
        );
    }

    /*
     * getDifficultyRange - get statistics on the range of route difficulties for a location
     * @param locationId int location id
     * @return array data of form array(difficulty_id, count)
     */
    public function getDifficultyRange($locationId) {
        return $this->SQL->fquery('
            SELECT r.difficulty_id, d.name, d.value, COUNT(*) AS `count`
            FROM routes r
            JOIN difficulties d
                ON d.id = r.difficulty_id
            WHERE r.location_id = %d
            GROUP BY r.difficulty_id
            ORDER BY d.value',
            $locationId
        );
    }

    public function statsGetDifficultyRange($locationId) {
        $data = $this->getDifficultyRange($locationId);
        if(!$data) {
            emptyimage();
        }

        $diffMgr = new Difficulty();
        $difficulties = $diffMgr->getAll();
        $labeledData = array();
        $labels = array();
        foreach($data as $row) {
            $labeledData[$row['difficulty_id']] = $row['count'];
            $labels[$row['difficulty_id']] = $difficulties[$row['difficulty_id']]['name'];
        }

        /* Create and populate the pData object */
        $MyData = new pData();
        $MyData->addPoints(array_values($labeledData), 'count');
        $MyData->setAxisName(0, 'Count');
        $MyData->addPoints(array_values($labels), 'diff_range');
        $MyData->setSerieDescription('diff_range', 'Difficulty');
        $MyData->setAbscissa('diff_range');
        $MyData->setAbscissaName('Difficulty');
        $MyData->setAxisDisplay(0, AXIS_FORMAT_METRIC, 1);

        $myCache = new pCache();
        $chartHash = $myCache->getHash($MyData);
        if($myCache->isInCache($chartHash) && !NOCACHE) {
            $myCache->autoOutput($chartHash, 'default.png');
            return;
        }

        $chartWidth = 275;
        $chartHeight = 45 + 32 * count($data);
        $chartMarginX = 50;
        $chartMarginY = 28;

        $myPicture = new pImage($chartWidth, $chartHeight, $MyData);
        $myPicture->setFontProperties(array('FontName' => './lib/pchart/fonts/verdana.ttf', 'FontSize' => 10));
        $myPicture->setGraphArea($chartMarginX, $chartMarginY, $chartWidth - $chartMarginX / 2, $chartHeight - $chartMarginY / 2);
        $myPicture->drawScale(array(
            'CycleBackground' => TRUE,
            'GridR' => 0, 'GridG' => 0, 'GridB' => 0,
            'GridAlpha' => 10,
            'Pos' => SCALE_POS_TOPBOTTOM,
        ));
        $myPicture->setShadow(TRUE, array(
            'X' => 1, 'Y' => 1,
            'R' => 0, 'G' => 0, 'B' => 0,
            'Alpha' => 10,
        ));
        $myPicture->drawBarChart(array(
            'DisplayPos' => LABEL_POS_INSIDE,
            'DisplayValues' => TRUE,
            'Rounded' => TRUE,
            'Surrounding' => 30,
        ));
        $myPicture->drawLegend(570, 215, array(
            'Style' => LEGEND_NOBORDER,
            'Mode' => LEGEND_HORIZONTAL,
        ));

        $myCache->writeToCache($chartHash, $myPicture);
        $myPicture->autoOutput('default.png');
    }
}
?>
